﻿Imports System.Data.SqlClient
Imports Microsoft.Office.Interop
Public Class FrmMucLucHoSoBienDong

    Private Sub NapDataGridView()
        Dim tbl As DataTable
        Dim dbAdapter As SqlDataAdapter
        Dim dbComm As SqlCommand
        dbComm = New SqlCommand("select   SohoSo,TenChu, DiaChi " &
                              " From TENCHUBIENDONG,  tenhuyen h" &
                              " where huyenid = '" & CmbHuyen.SelectedValue.ToString & "' " &
                              " and huyenid = h.mah " &
                              " and TenChu like N'%" & TextTenChu.Text & "%' ", ob_cnn)
        dbAdapter = New SqlDataAdapter(dbComm)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        DataGridViewTenChu.DataSource = tbl
        If tbl.Rows.Count = 0 Then
            MessageBox.Show("Không tìm thấy thông tin ! ")
        End If
        ob_cnn.Close()
    End Sub

    Private Sub NapDataGridViewSoHoSo()
        Dim tbl As DataTable
        Dim dbAdapter As SqlDataAdapter
        Dim dbComm As SqlCommand
        dbComm = New SqlCommand("select  SohoSo,TenChu, DiaChi " &
                              " From TENCHUBIENDONG,  tenhuyen h" &
                              " where huyenid = '" & CmbHuyen.SelectedValue.ToString & "' " &
                              " and huyenid = h.mah " &
                              " and sohoso =" & TextSoHoSo.Text & " ", ob_cnn)
        dbAdapter = New SqlDataAdapter(dbComm)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        DataGridViewTenChu.DataSource = tbl
        If tbl.Rows.Count = 0 Then
            MessageBox.Show("Không tìm thấy thông tin ! ")
        End If
        ob_cnn.Close()
    End Sub
    Private Sub NapDataGridViewtenchu()
        Dim tbl As DataTable
        Dim dbAdapter As SqlDataAdapter
        Dim dbComm As SqlCommand
        dbComm = New SqlCommand("select  TenChu, DiaChi,  SohoSo " &
                                " From TENCHUBIENDONG,TENHUYEN h " &
                                " where huyenid = h.mah " &
                                " and huyenid = '" & CmbHuyen.SelectedValue.ToString & "' " &
                                " and TenChu like N'%" & TextTenChu.Text & "%' ", ob_cnn) '
        dbAdapter = New SqlDataAdapter(dbComm)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        DataGridViewTenChu.DataSource = tbl
        ob_cnn.Close()
    End Sub
    Private Sub NapDataGridViewThuaDat()
        Dim tbl As DataTable
        Dim dbAdapter As SqlDataAdapter
        Dim dbComm As SqlCommand
        dbComm = New SqlCommand("select thua.thuaid, thua.sohoso, thua.thuadat, thua.tobando, thua.dientich, thua.loaidat, " &
                                " tl.tenloaitailieu " &
                                " From  THUADATBIENDONG thua,  DMTailieu tl  " &
                                 " WHERE thua.sohoso = '" & TextSoHoSo.Text & "' " &
                                 " and thua.loaitailieu = tl.maloaitailieuID ", ob_cnn)
        dbAdapter = New SqlDataAdapter(dbComm)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        DataGridViewThuaDat.DataSource = tbl
        ob_cnn.Close()
    End Sub
    Private Sub NapVaoTexBox()
        TextSoHoSo.DataBindings.Clear()
        TextSoHoSo.DataBindings.Add("Text", DataGridViewTenChu.DataSource, "SoHoSo")
        TextTenChu.DataBindings.Clear()
        TextTenChu.DataBindings.Add("Text", DataGridViewTenChu.DataSource, "tenchu")
        TextDiaChi.DataBindings.Clear()
        TextDiaChi.DataBindings.Add("Text", DataGridViewTenChu.DataSource, "diachi")
        NapDataGridViewThuaDat()
        NapDataGridViewVanBan()
    End Sub
    Private Sub Lamtuoi()
        TextSoHoSo.Text = ""
        TextTenChu.Text = ""
        TextDiaChi.Text = ""
    End Sub
    Private Sub Them_Du_lieu()
        Try
            Dim tennhap As String
            tennhap = ModuleTenDangNhap.m_tennhap
            Dim NgayNhap As String
            NgayNhap = String.Format("{0:MM/dd/yyyy}", Ngayky.Value)
            Dim Sql As String = ""
            Sql = " insert into MucLucBienDong([SoHoSo]" &
           ",[KyHieuVanBan]" &
           ",[NgayKy]" &
           ",[TenLoaiVanBanID]" &
           ",[BanGoc]" &
           ",[BanSao]" &
           ",[nguoinhap]" &
           ",[ngaynhap]" &
           ",[TenHuyenID])" &
           " values('" & TextSoHoSo.Text & "', " &
                " N'" & TextboxKyHieu.Text & "', " &
                " '" & NgayNhap & "', " &
                " '" & CmbTenVanBan.SelectedValue.ToString & "'," &
                " '" & TextBoxBanGoc.Text & "', " &
                " '" & TextBoxBanSao.Text & "'," &
                " '" & tennhap & "'," &
                "  " & Format(Now.ToOADate() - 2) & " ," &
                " '" & CmbHuyen.SelectedValue.ToString & "')"
            Dim cmd As New SqlCommand(Sql, ob_cnn)
            ob_cnn.Open()
            cmd.ExecuteNonQuery()
            ob_cnn.Close()
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
    End Sub
    Private Sub NapDataGridViewVanBan()
        Dim sql As String
        sql = "select  ml.KyHieuVanBan, ml.ngayky, vb.TenVanBan, ml.BanGoc, ml.BanSao, MamuclucID " &
                                " From MucLucBienDong ml,  DMTenVanBan vb" &
                                " where ml.tenloaivanbanid= vb.maTenvanban " &
                                " and ml.sohoso ='" & TextSoHoSo.Text & "' " &
                                " and TenHuyenID = '" & CmbHuyen.SelectedValue.ToString & "' ORDER BY MamuclucID"

        DataGridViewVanBan.DataSource = LoadThongTin(sql)
        DataGridViewVanBan.Refresh()
        ob_cnn.Close()
    End Sub
    Private Sub Capnhatthongtin()
        Try
            Dim NgayNhap As String
            NgayNhap = String.Format("{0:MM/dd/yyyy}", Ngayky.Value)
            Dim Sql As String = ""
            Sql = " Update MucLucBienDong Set kyhieuvanban = '" & TextboxKyHieu.Text & "'," &
                " NgayKy = '" & NgayNhap & "'," &
                " BanGoc = '" & TextBoxBanGoc.Text & "'," &
                " BanSao = '" & TextBoxBanSao.Text & "'," &
                " tenloaivanbanid = '" & CmbTenVanBan.SelectedValue.ToString & "' where MamuclucID = '" & TextBoxTT.Text & "'"
            Dim cmd As New SqlCommand(Sql, ob_cnn)
            ob_cnn.Open()
            cmd.ExecuteNonQuery()
            ob_cnn.Close()

            MsgBox("Sửa thông tin thành công!")

        Catch ex As Exception
            MsgBox(ex.Message.ToString)
        End Try
    End Sub
    Private Sub ButtonTimKiem_Click(sender As System.Object, e As System.EventArgs) Handles ButtonTimKiem.Click
        '  Lamtuoi()
        NapDataGridView()
    End Sub

    Public Sub FrmMucLucHoSoBienDong_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
        '   btnLuuFile.Enabled = False
        LoadHuyen(CmbHuyen)
        LoadTenVanBan(CmbTenVanBan)
        DataGridViewTenChu.RowsDefaultCellStyle.BackColor = Color.Beige
        DataGridViewTenChu.AlternatingRowsDefaultCellStyle.BackColor = Color.Azure
        DataGridViewThuaDat.RowsDefaultCellStyle.BackColor = Color.Beige
        DataGridViewThuaDat.AlternatingRowsDefaultCellStyle.BackColor = Color.Azure
        DataGridViewVanBan.RowsDefaultCellStyle.BackColor = Color.Beige
        DataGridViewVanBan.AlternatingRowsDefaultCellStyle.BackColor = Color.Azure
    End Sub

    Private Sub ButtonThoat_Click(sender As System.Object, e As System.EventArgs) Handles ButtonThoat.Click
        Me.Close()
    End Sub

    Private Sub DataGridViewTenChu_CellClick(sender As System.Object, e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridViewTenChu.CellClick
        NapVaoTexBox()
        ' NapDataGridViewVanBan()

    End Sub
    Private Sub DataGridViewTenChu_RowPostPaint(sender As Object, e As DataGridViewRowPostPaintEventArgs) Handles DataGridViewTenChu.RowPostPaint
        Dim strRowNumber As String = (e.RowIndex + 1).ToString
        While (strRowNumber.Length < DataGridViewTenChu.RowCount.ToString.Length)
            strRowNumber = "0" & strRowNumber
        End While
        Dim Size As SizeF = e.Graphics.MeasureString(strRowNumber, MyBase.Font)
        If DataGridViewTenChu.RowHeadersWidth < Size.Width + 20 Then DataGridViewTenChu.RowHeadersWidth = Size.Width + 20
        Dim b As Brush = SystemBrushes.ControlText
        e.Graphics.DrawString(strRowNumber, MyBase.Font, b, e.RowBounds.Location.X + 15, e.RowBounds.Location.Y +
                              ((e.RowBounds.Height - Size.Height) / 2))
    End Sub
    Private Sub DataGridViewThuaDat_RowPostPaint(sender As Object, e As DataGridViewRowPostPaintEventArgs) Handles DataGridViewThuaDat.RowPostPaint
        Dim strRowNumber As String = (e.RowIndex + 1).ToString
        While (strRowNumber.Length < DataGridViewThuaDat.RowCount.ToString.Length)
            strRowNumber = "0" & strRowNumber
        End While
        Dim Size As SizeF = e.Graphics.MeasureString(strRowNumber, MyBase.Font)
        If DataGridViewThuaDat.RowHeadersWidth < Size.Width + 20 Then DataGridViewThuaDat.RowHeadersWidth = Size.Width + 20
        Dim b As Brush = SystemBrushes.ControlText
        e.Graphics.DrawString(strRowNumber, MyBase.Font, b, e.RowBounds.Location.X + 15, e.RowBounds.Location.Y +
                              ((e.RowBounds.Height - Size.Height) / 2))
    End Sub

    Private Sub ButtonLuu_Click(sender As Object, e As EventArgs) Handles ButtonLuu.Click
        Them_Du_lieu()
        NapDataGridViewVanBan()
    End Sub
    Private Sub DataGridViewVanBan_CellClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridViewVanBan.CellClick

    End Sub

    Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
        capnhatthongtin()
        NapDataGridViewVanBan()
    End Sub

    Private Sub DataGridViewVanBan_CellContentClick(sender As Object, e As DataGridViewCellEventArgs) Handles DataGridViewVanBan.CellContentClick
        Dim dong As Integer
        dong = e.RowIndex
        'Kiem tra phai dong tieu de ko
        If (dong = -1) Then
            ' MessageBox.Show("Không tìm thấy thông tin !")
            Return
        End If
        TextBoxTT.DataBindings.Clear()
        TextBoxTT.DataBindings.Add("Text", DataGridViewVanBan.DataSource, "MamuclucID")
        TextboxKyHieu.DataBindings.Clear()
        TextboxKyHieu.DataBindings.Add("Text", DataGridViewVanBan.DataSource, "KyHieuVanBan")
        Ngayky.Text = String.Format("{0:dd/MM/yyyy}", DataGridViewVanBan.Rows(dong).Cells("ngay").Value.ToString())

        CmbTenVanBan.DataBindings.Clear()
        CmbTenVanBan.DataBindings.Add("Text", DataGridViewVanBan.DataSource, "Tenvanban")
        TextBoxBanGoc.DataBindings.Clear()
        TextBoxBanGoc.DataBindings.Add("Text", DataGridViewVanBan.DataSource, "BanGoc")
        TextBoxBanSao.DataBindings.Clear()
        TextBoxBanSao.DataBindings.Add("Text", DataGridViewVanBan.DataSource, "BanSao")
    End Sub
    Private Sub TextTenChu_TextChanged(sender As Object, e As EventArgs) Handles TextTenChu.TextChanged

    End Sub
    Private Sub TextSoHoSo_TextChanged(sender As Object, e As EventArgs) Handles TextSoHoSo.TextChanged
        NapDataGridViewSoHoSo()
        '  btnLuuFile.Enabled = True
    End Sub

    Private Sub DataGridViewVanBan_RowPostPaint(sender As Object, e As DataGridViewRowPostPaintEventArgs) Handles DataGridViewVanBan.RowPostPaint
        Dim strRowNumber As String = (e.RowIndex + 1).ToString
        While (strRowNumber.Length < DataGridViewVanBan.RowCount.ToString.Length)
            strRowNumber = "0" & strRowNumber
        End While
        Dim Size As SizeF = e.Graphics.MeasureString(strRowNumber, MyBase.Font)
        If DataGridViewVanBan.RowHeadersWidth < Size.Width + 20 Then DataGridViewVanBan.RowHeadersWidth = Size.Width + 20
        Dim b As Brush = SystemBrushes.ControlText
        e.Graphics.DrawString(strRowNumber, MyBase.Font, b, e.RowBounds.Location.X + 15, e.RowBounds.Location.Y +
                              ((e.RowBounds.Height - Size.Height) / 2))
    End Sub


    Private Sub ButtonInPhieu_Click(sender As Object, e As EventArgs) Handles ButtonInPhieu.Click
        Dim wa As New Word.Application
        wa.Documents.Add()
        Dim wDocument As Word.Document = wa.Documents.Item(1)
        ' Định dạng khổ giấy là A4 và giấy dọc
        wDocument.PageSetup.PaperSize = Word.WdPaperSize.wdPaperA4
        wDocument.PageSetup.Orientation = Word.WdOrientation.wdOrientPortrait
        ' Định dạng lề trên, dưới, trái, phải
        wDocument.PageSetup.TopMargin = 56.8
        wDocument.PageSetup.BottomMargin = 56.8
        wDocument.PageSetup.LeftMargin = 85
        wDocument.PageSetup.RightMargin = 56.8
        Dim wSelection As Word.Selection
        wSelection = wDocument.Application.Selection
        With wSelection
            ' .TypeParagraph()
            ' Định dạng font
            .Font.Name = "Times New Roman"
            .Font.Color = Word.WdColor.wdColorBlack
            .Font.Size = 16
            .Font.Bold = True
            .Paragraphs.Alignment = Word.WdParagraphAlignment.wdAlignParagraphCenter
            .TypeText("MỤC LỤC HỒ SƠ")

            .TypeParagraph()
            .Font.Size = 14
            .Font.Bold = False
            .Paragraphs.Alignment = Word.WdParagraphAlignment.wdAlignParagraphLeft
            .Range.ParagraphFormat.SpaceAfter = Word.WdLineSpacing.wdLineSpaceMultiple
            .TypeText(vbTab & vbTab & "Mã hồ sơ lưu kho: " & TextSoHoSo.Text)
            .TypeParagraph()
            .TypeText(vbTab & vbTab & "Đơn vị: ")
            .Font.Bold = True
            .TypeText(TextTenChu.Text)
            .Font.Bold = False
            .TypeParagraph()
            .TypeText(vbTab & vbTab & "Địa chỉ thường trú: " & TextDiaChi.Text)
            .TypeParagraph()
            ' Tạo một dataTable 
            Dim cmd As SqlCommand
            Dim sql As String
            sql = "select  ml.KyHieuVanBan, FORMAT(ml.ngayky,'dd/MM/yyyy') as ngayky, vb.TenVanBan, ml.BanGoc, ml.BanSao " &
                                " From MucLucBienDong ml,  DMTenVanBan vb" &
                                " where ml.tenloaivanbanid= vb.maTenvanban " &
                                " and ml.sohoso ='" & TextSoHoSo.Text & "' " &
                                " and TenHuyenID = '" & CmbHuyen.SelectedValue.ToString & "'"
            cmd = New SqlCommand(sql, ob_cnn)
            Dim dataTable As New DataTable
            ob_cnn.Open()
            dataTable.Load(cmd.ExecuteReader())
            ob_cnn.Close()
            ' Tạo một table và đặt lại font khác

            Dim soDong As Integer = 10
            .Tables.Add(.Range, dataTable.Rows.Count + 2, 7)

            ' Định dạng bảng
            With wSelection.Tables.Item(1)
                ' Canh bảng ở giữa trang giấy
                .Range.ParagraphFormat.SpaceAfter = Word.WdLineSpacing.wdLineSpaceSingle
                '.Rows.Alignment = Word.WdRowAlignment.wdAlignRowCenter
                ' .Cell(0, 0).VerticalAlignment = Word.WdCellVerticalAlignment.wdCellAlignVerticalCenter
                '.Cell(1, 1).Range.Paragraphs.Format.Alignment = Word.WdParagraphAlignment.wdAlignParagraphCenter
                '.Cell(1, 1).Range.Paragraphs.Alignment = Word.WdParagraphAlignment.wdAlignParagraphCenter
                ' Định dạng border
                .Borders.InsideLineStyle = Word.WdLineStyle.wdLineStyleSingle
                .Borders.OutsideLineStyle = Word.WdLineStyle.wdLineStyleSingle
                ' Set độ rộng các cột
                .Columns.Item(1).Width = 27
                .Columns.Item(2).Width = 52
                .Columns.Item(3).Width = 71
                .Columns.Item(4).Width = 178
                .Columns.Item(5).Width = 50
                .Columns.Item(6).Width = 50
                .Columns.Item(7).Width = 43
                ' Gõ text vào các ô tiêu đề
                .Range.Font.Size = 12
                .Range.Font.Bold = True
                .Range.Paragraphs.Alignment = Word.WdParagraphAlignment.wdAlignParagraphCenter
                .Cell(1, 1).Range.Text = "Số TT"
                .Cell(1, 2).Range.Text = "Số và ký hiệu văn bản"
                .Cell(1, 3).Range.Text = "Ngày tháng văn bản"
                .Cell(1, 4).Range.Text = "Tên loại và trích yếu văn bản"
                .Cell(1, 5).Range.Text = "Bản gốc"
                .Cell(1, 6).Range.Text = "Bản photo"
                .Cell(1, 7).Range.Text = "Ghi chú"
                ' Gán dữ liệu vào bảng trong file Word
                For dong = 0 To dataTable.Rows.Count - 1
                    For cot = 0 To dataTable.Columns.Count - 1
                        .Cell(dong + 2, cot + 1).Range.Bold = False
                        .Cell(dong + 2, 6).Range.Bold = False
                        .Cell(dong + 2, 7).Range.Bold = False
                        .Cell(dong + 2, 1).Range.Text = dong + 1
                        .Cell(dong + 2, cot + 2).Range.Paragraphs.Alignment = Word.WdParagraphAlignment.wdAlignParagraphCenter
                        .Cell(dong + 2, cot + 2).Range.Text = dataTable.Rows(dong)(cot).ToString
                    Next
                Next
                For index = 2 To dataTable.Rows.Count + 1
                    .Cell(index, 4).Range.Paragraphs.Alignment = Word.WdParagraphAlignment.wdAlignParagraphLeft
                Next
                For dong = 1 To dataTable.Rows.Count + 2
                    For cot = 1 To dataTable.Columns.Count + 2
                        .Rows.Height = 25
                        .Cell(dong, cot).VerticalAlignment = Word.WdCellVerticalAlignment.wdCellAlignVerticalCenter
                    Next
                Next
                .Rows.Item(dataTable.Rows.Count + 2).Height = 40
                .Cell(dataTable.Rows.Count + 2, 1).Merge(.Cell(dataTable.Rows.Count + 2, 3))
                .Cell(dataTable.Rows.Count + 2, 1).Range.Paragraphs.Alignment = Word.WdParagraphAlignment.wdAlignParagraphCenter
                .Cell(dataTable.Rows.Count + 2, 1).Range.Font.Bold = True
                .Cell(dataTable.Rows.Count + 2, 1).Range.Text = "Tổng cộng"
            End With
        End With
        wa.ActiveWindow.Visible = True
    End Sub

    Private Sub Button4_Click(sender As Object, e As EventArgs) Handles Button4.Click
        FrmLVanBan.Show()
    End Sub
    Private Sub XoaMucLuc()
        Dim sql As String
        sql = "Delete from MucLucBienDong where MamuclucID = '" & TextBoxTT.Text & "' and SoHoSo = " & TextSoHoSo.Text & ""
        Dim cmd As New SqlCommand(sql, ob_cnn)
        ob_cnn.Open()
        cmd.ExecuteNonQuery()
        ob_cnn.Close()

    End Sub

    Private Sub btnXoa_Click(sender As Object, e As EventArgs) Handles btnXoa.Click
        If TextBoxTT.Text = "" Then
            MessageBox.Show("Chọn dòng cần xóa")
            Return
        End If
        If TextSoHoSo.Text = "" Then
            MessageBox.Show("Chọn dòng cần xóa")
            Return
        End If
        Dim Msg As String
        Msg = MessageBox.Show("Bạn có thực sự muốn xóa không ?", "Lưu ý", MessageBoxButtons.YesNo, MessageBoxIcon.Question)
        If Msg = Windows.Forms.DialogResult.Yes Then
            XoaMucLuc()
            TextBoxTT.Text = ""
            NapDataGridViewVanBan()
        End If
    End Sub

    Private Sub btnLuuAnh_Click(sender As Object, e As EventArgs)

    End Sub

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        _sohoso = TextSoHoSo.Text
        Dim f As FrmLuuHoSoScan = New FrmLuuHoSoScan()
        f.ShowDialog()
    End Sub

    Private Sub ButtonMofile_Click(sender As Object, e As EventArgs)
        'OpenFileDialog1.Filter = "PDF Files(*.pdf)|*.pdf|All Files(*.*)|*.*"
        'If OpenFileDialog1.ShowDialog = DialogResult.OK Then
        '    TextBoxDuongDan.Text = OpenFileDialog1.FileName
        '    '    AxAcroPDF1.src = OpenFileDialog2.FileName
        'End If
    End Sub
End Class